
import sqlite3
from datetime import datetime
from pathlib import Path
import os


DB_PATH = Path(__file__).parent / 'vocabulary.db'


class Database:

    def __init__(self, db_path=None):
        self.db_path = db_path or DB_PATH

    def get_connection(self):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn

    def add_word(self, french, chinese, category='翻译结果', user_id=None, audio_url=None):
        conn = self.get_connection()
        cursor = conn.cursor()

        if user_id:

            cursor.execute('''
                INSERT INTO vocabulary_new (user_id, french, chinese, category, audio_url, created_at)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (user_id, french, chinese, category, audio_url, datetime.now().isoformat()))
        else:

            cursor.execute('''
                INSERT INTO vocabulary (french, chinese, category, created_at)
                VALUES (?, ?, ?, ?)
            ''', (french, chinese, category, datetime.now().isoformat()))

        word_id = cursor.lastrowid
        conn.commit()
        conn.close()

        return word_id

    def get_user_words(self, user_id, order_by='created_at', order='DESC'):
        conn = self.get_connection()
        cursor = conn.cursor()

        query = f'''
            SELECT * FROM vocabulary_new
            WHERE user_id = ?
            ORDER BY {order_by} {order}
        '''

        cursor.execute(query, (user_id,))
        rows = cursor.fetchall()
        conn.close()

        words = []
        for row in rows:
            words.append({
                'id': row['id'],
                'french': row['french'],
                'chinese': row['chinese'],
                'category': row['category'],
                'audio_url': row['audio_url'] if 'audio_url' in row.keys() else None,
                'created_at': row['created_at']
            })

        return words

    def get_all_words(self, order_by='created_at', order='DESC'):
        conn = self.get_connection()
        cursor = conn.cursor()

        query = f'''
            SELECT * FROM vocabulary
            ORDER BY {order_by} {order}
        '''

        cursor.execute(query)
        rows = cursor.fetchall()
        conn.close()


        words = []
        for row in rows:
            words.append({
                'id': row['id'],
                'french': row['french'],
                'chinese': row['chinese'],
                'category': row['category'],
                'created_at': row['created_at']
            })

        return words

    def get_word(self, word_id):
        conn = self.get_connection()
        cursor = conn.cursor()


        cursor.execute('SELECT * FROM vocabulary_new WHERE id = ?', (word_id,))
        row = cursor.fetchone()

        if row:
            conn.close()
            return {
                'id': row['id'],
                'french': row['french'],
                'chinese': row['chinese'],
                'category': row['category'],
                'audio_url': row['audio_url'] if 'audio_url' in row.keys() else None,
                'created_at': row['created_at']
            }


        cursor.execute('SELECT * FROM vocabulary WHERE id = ?', (word_id,))
        row = cursor.fetchone()
        conn.close()

        if row:
            return {
                'id': row['id'],
                'french': row['french'],
                'chinese': row['chinese'],
                'category': row['category'],
                'audio_url': None,
                'created_at': row['created_at']
            }
        return None

    def update_word(self, word_id, french=None, chinese=None, category=None, audio_url=None):
        conn = self.get_connection()
        cursor = conn.cursor()


        cursor.execute('SELECT id FROM vocabulary_new WHERE id = ?', (word_id,))
        if cursor.fetchone():

            cursor.execute('SELECT * FROM vocabulary_new WHERE id = ?', (word_id,))
            word = dict(cursor.fetchone())


            new_french = french if french is not None else word['french']
            new_chinese = chinese if chinese is not None else word['chinese']
            new_category = category if category is not None else word['category']
            new_audio = audio_url if audio_url is not None else word.get('audio_url')

            cursor.execute('''
                UPDATE vocabulary_new
                SET french = ?, chinese = ?, category = ?, audio_url = ?
                WHERE id = ?
            ''', (new_french, new_chinese, new_category, new_audio, word_id))
        else:

            cursor.execute('SELECT * FROM vocabulary WHERE id = ?', (word_id,))
            word = dict(cursor.fetchone())

            new_french = french if french is not None else word['french']
            new_chinese = chinese if chinese is not None else word['chinese']
            new_category = category if category is not None else word['category']

            cursor.execute('''
                UPDATE vocabulary
                SET french = ?, chinese = ?, category = ?
                WHERE id = ?
            ''', (new_french, new_chinese, new_category, word_id))

        conn.commit()
        conn.close()

    def delete_word(self, word_id):
        conn = self.get_connection()
        cursor = conn.cursor()

        cursor.execute('DELETE FROM vocabulary WHERE id = ?', (word_id,))

        conn.commit()
        conn.close()

    def search_words(self, keyword, user_id=None):
        conn = self.get_connection()
        cursor = conn.cursor()

        search_pattern = f'%{keyword}%'

        if user_id:

            query = '''
                SELECT * FROM vocabulary_new
                WHERE user_id = ? AND (french LIKE ? OR chinese LIKE ?)
                ORDER BY created_at DESC
            '''
            cursor.execute(query, (user_id, search_pattern, search_pattern))
        else:

            query = '''
                SELECT * FROM vocabulary
                WHERE french LIKE ? OR chinese LIKE ?
                ORDER BY created_at DESC
            '''
            cursor.execute(query, (search_pattern, search_pattern))

        rows = cursor.fetchall()
        conn.close()

        words = []
        for row in rows:
            words.append({
                'id': row['id'],
                'french': row['french'],
                'chinese': row['chinese'],
                'category': row['category'],
                'audio_url': row['audio_url'] if 'audio_url' in row.keys() else None,
                'created_at': row['created_at']
            })

        return words

    def get_statistics(self, user_id=None):
        conn = self.get_connection()
        cursor = conn.cursor()

        if user_id:


            cursor.execute('SELECT COUNT(*) as total FROM vocabulary_new WHERE user_id = ?', (user_id,))
            total = cursor.fetchone()['total']


            cursor.execute('''
                SELECT category, COUNT(*) as count
                FROM vocabulary_new
                WHERE user_id = ?
                GROUP BY category
                ORDER BY count DESC
            ''', (user_id,))
            by_category = [dict(row) for row in cursor.fetchall()]


            today = datetime.now().strftime('%Y-%m-%d')
            cursor.execute('''
                SELECT COUNT(*) as today_count
                FROM vocabulary_new
                WHERE user_id = ? AND DATE(created_at) = ?
            ''', (user_id, today))
            today_count = cursor.fetchone()['today_count']
        else:


            cursor.execute('SELECT COUNT(*) as total FROM vocabulary')
            total = cursor.fetchone()['total']


            cursor.execute('''
                SELECT category, COUNT(*) as count
                FROM vocabulary
                GROUP BY category
                ORDER BY count DESC
            ''')
            by_category = [dict(row) for row in cursor.fetchall()]


            today = datetime.now().strftime('%Y-%m-%d')
            cursor.execute('''
                SELECT COUNT(*) as today_count
                FROM vocabulary
                WHERE DATE(created_at) = ?
            ''', (today,))
            today_count = cursor.fetchone()['today_count']

        conn.close()

        return {
            'total': total,
            'by_category': by_category,
            'today_count': today_count
        }


def init_db():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()


    cursor.execute('''
        CREATE TABLE IF NOT EXISTS vocabulary (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            french TEXT NOT NULL,
            chinese TEXT NOT NULL,
            category TEXT DEFAULT '翻译结果',
            created_at TEXT NOT NULL,
            UNIQUE(french, chinese)
        )
    ''')


    cursor.execute('''
        CREATE TABLE IF NOT EXISTS vocabulary_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            french TEXT NOT NULL,
            chinese TEXT NOT NULL,
            category TEXT DEFAULT '翻译结果',
            audio_url TEXT,
            created_at TEXT NOT NULL
        )
    ''')


    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_french ON vocabulary(french)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_chinese ON vocabulary(chinese)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_created_at ON vocabulary(created_at)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_vocab_user ON vocabulary_new(user_id)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_vocab_new_french ON vocabulary_new(french)
    ''')

    conn.commit()
    conn.close()

    print(f"✓ 数据库已初始化: {DB_PATH}")


if __name__ == '__main__':

    print("="*60)
    print("🧪 数据库测试")
    print("="*60)


    init_db()

    db = Database()


    print("\n➕ 添加测试数据...")
    word_id = db.add_word(
        french="Bonjour",
        chinese="你好",
        category="问候语"
    )
    print(f"   添加成功，ID: {word_id}")


    print("\n📋 查询所有单词...")
    words = db.get_all_words()
    for word in words:
        print(f"   {word['id']}. {word['french']} - {word['chinese']} [{word['category']}]")


    print("\n🔍 搜索测试...")
    results = db.search_words("你好")
    print(f"   找到 {len(results)} 条结果")


    print("\n📊 统计信息...")
    stats = db.get_statistics()
    print(f"   总数: {stats['total']}")
    print(f"   今日新增: {stats['today_count']}")

    print("\n✅ 测试完成")
